package com.work.count.util;

import com.work.count.pojo.IoNumberInfo;
import com.work.count.pojo.WorkTime;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ReadXlsxUtil {
    private static NumberFormat numberFormat = NumberFormat.getInstance( );

    static {
        numberFormat.setGroupingUsed(false);
    }

    public static List<List> readXlsx(FileInputStream file){
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ArrayList<List> listAll = new ArrayList<>();

        List<WorkTime> workTimeList = new ArrayList<>();
        XSSFSheet sheetAt = wb.getSheetAt(0);
        List<IoNumberInfo> listIoNumberInfo = readIoNumberInfo(sheetAt);
            listAll.add(listIoNumberInfo);
            XSSFSheet sheet = wb.getSheetAt(1);
            //获取第一行
            //题头 基本信息表头
        XSSFRow row1 = sheet.getRow(0);
        //列的总长度
            short lastCellNum1 = row1.getLastCellNum();
            List<String> list = new ArrayList<>();
            for (int rowLength = 0; rowLength < lastCellNum1; rowLength++) {
                if (rowLength>=3) {
                    Date dateCellValue = row1.getCell(rowLength).getDateCellValue();
                    String s = dateFormat(dateCellValue);
                    list.add(s);
                }else {
                    list.add(row1.getCell(rowLength).toString());
                }
            }
            //获取最后一行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {

                Row row = sheet.getRow(i);
                if (null != row) {

                    short lastCellNum = row.getLastCellNum();
                    for (int j = 4; j < lastCellNum; j++) {
                        String cell = row.getCell(j).toString();
                        String workHour = "" != cell ? cell : "0";
                        WorkTime workTime = new WorkTime();
                        workTime.setIoNumber(row.getCell(1)+"");
                        workTime.setDeviceName(row.getCell(2)+"");
                        workTime.setUserName(row.getCell(3)+"");
                        workTime.setWorkHour(workHour);
                        workTime.setDateTime(list.get(j-1));
                        workTimeList.add(workTime);
                        System.out.println(cell.toString());
                    }
                }
            }
        listAll.add(workTimeList);
        return listAll;
    }



    /**
     * @description:  读取第一张表格的数据 
     * @param sheet
     * @return: java.util.List<com.work.count.pojo.IoNumberInfo>
     * @author: Sue
     * @date: 2021/5/28 0028 21:23
     */
    public static List<IoNumberInfo> readIoNumberInfo(Sheet sheet){
        List<IoNumberInfo> list = new ArrayList<>();
        //获取最后一行
        int lastRowNum = sheet.getLastRowNum();

        for (int i = 1; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (null == row){
                return list;
            }
            IoNumberInfo info = new IoNumberInfo();
            String costCenter = row.getCell(0)+"";
            /*double numericCellValue = row.getCell(0).getNumericCellValue();
            String costCenter = numberFormat.format(numericCellValue);*/
            info.setCostCenter(costCenter);
            String ioNumber = row.getCell(1)+"";
            info.setIoNumber(ioNumber);
            String deviceName = row.getCell(2) + "";
            info.setDeviceName(deviceName);
            String num = row.getCell(3)+"";
            info.setCrNum(num);
            String allNum = row.getCell(4).getNumericCellValue()+"";
            info.setAllNum(allNum);
            list.add(info);
        }
        return list;
    }

    public static String dateFormat(Date dateTime){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        String dateFormat = sdf.format(dateTime);
        return dateFormat;
    }
}
